package com.yixing.tech.common.utils;

import com.yixing.tech.common.base.BusinessException;
import com.yixing.tech.common.enums.ErrCode;
import com.yixing.tech.common.service.CommonService;
import com.yixing.tech.common.vo.s5.EntitySchema;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.bson.Document;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * excel工具类
 */
@Slf4j
public class ExcelUtil {
    /**
     * 将Excel内容转换list
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> excelToList(MultipartFile file) throws Exception {
        return excelToList(file, null);
    }


    /**
     * 将Excel内容转换list,属性中文key转英文key
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> excelToList(MultipartFile file, List<EntitySchema.AttrVO> attrs) throws Exception {
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        //行数
        int num = sheet.getLastRowNum();
        //列数
        int col = sheet.getRow(0).getLastCellNum();
        List<Map<String, Object>> list = new ArrayList<>();
        String[] colName = new String[col];
        //获取列名
        Row row = sheet.getRow(0);
        for (int i = 0; i < col; i++) {
            String[] s = row.getCell(i).getStringCellValue().split("-");
            if (attrs != null) {
                String key = getAttrCode(s[0], attrs);
                colName[i] = StringUtils.isEmpty(key) ? s[0] : key;
            } else {
                colName[i] = s[0];
            }

        }

        //将一行中每列数据放入一个map中,然后把map放入list
        label:
        for (int i = 1; i <= num; i++) {
            Row row1 = sheet.getRow(i);
            if (row1 == null) continue;
            int nullCount = 0;
            boolean isNull = true;
            Map<String, Object> map = new HashMap<>();
            for (int j = 0; j < col; j++) {
                if (nullCount > 3 && isNull) continue label;
                Cell cell = row1.getCell(j);
                if (cell != null) {
                    CellType cellType = cell.getCellType();
                    if (cellType == CellType.NUMERIC) {
                        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            map.put(colName[j], date);
                        } else {
                            double numericCellValue = cell.getNumericCellValue();
                            String s = new BigDecimal(String.valueOf(numericCellValue)).stripTrailingZeros().toPlainString();
                            map.put(colName[j], s);
                        }

                    } else if (cellType == CellType.FORMULA) {
                        Object o = null;
                        try {
                            o = cell.getNumericCellValue();
                        } catch (Exception e) {
                            o = cell.getStringCellValue();
                        }
                        map.put(colName[j], o);
                    } else {
                        map.put(colName[j], cell.getStringCellValue().trim());
                    }
                    Object v = map.get(colName[j]);
                    if (ObjectUtils.isEmpty(v)) {
                        nullCount++;
                    } else {
                        isNull = false;
                    }
                } else {
                    nullCount++;
                }
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 返回属性编码
     *
     * @param name
     * @param attrs
     * @return
     */
    private static String getAttrCode(String name, List<EntitySchema.AttrVO> attrs) {
        for (EntitySchema.AttrVO attrVO : attrs) {
            if (name.equals(attrVO.getName())) {
                return attrVO.getCode();
            }
        }
        return name;
    }

    /**
     * 返回属性编码
     *
     * @param code
     * @param attrs
     * @return
     */
    private static EntitySchema.AttrVO getAttr(String code, List<EntitySchema.AttrVO> attrs) {
        for (EntitySchema.AttrVO attrVO : attrs) {
            if (code.equals(attrVO.getCode())) {
                return attrVO;
            }
        }
        return new EntitySchema.AttrVO();
    }


    /**
     * 返回属性编码
     *
     * @param attrs
     * @return
     */
    private static String getAttrName(String code, List<EntitySchema.AttrVO> attrs) {
        for (EntitySchema.AttrVO attrVO : attrs) {
            if (code.equals(attrVO.getCode())) {
                return attrVO.getName();
            }
        }
        return code;
    }

    /**
     * 返回子表单类型
     *
     * @param attrs
     * @return
     */
    private static EntitySchema.AttrVO getSubArrayCode(List<EntitySchema.AttrVO> attrs) {
        for (EntitySchema.AttrVO attrVO : attrs) {
            if ("objArray".equals(attrVO.getType()) || "multiObject".equals(attrVO.getType())) {
                return attrVO;
            }
        }
        return null;
    }

    /**
     * 返回属性顺序
     *
     * @param attrs
     * @return
     */
    private static double getAttrSort(String code, List<EntitySchema.AttrVO> attrs) {
        for (EntitySchema.AttrVO attrVO : attrs) {
            if (code.equals(attrVO.getCode())) {
                return attrVO.getSort();
            }
        }
        return 0;
    }

    public static void exportToExcel(HttpServletResponse response, String name, List<Map> list, boolean enableTitle) throws BusinessException {
        response.setHeader("Access-Control-Expose-Headers", "content-disposition");
        response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
        // 下载文件的默认名称
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            throw new BusinessException(ErrCode.EXPORT_ERROR);
        }
        exportToExcel1(response, name, list, enableTitle);
    }

    public static void exportToExcel(HttpServletResponse response, String name, List<Map> list) throws BusinessException {
        exportToExcel(response, name, list, true);
    }

    /**
     * 导出集合到excel
     *
     * @param response
     * @param name
     * @param list
     */
    private static void exportToExcel1(HttpServletResponse response, String name, List<Map> list, boolean enableTitle) {
        try {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            HSSFSheet hssfSheet = hssfWorkbook.createSheet(name);
            int rowNum = 0;
            //新建行
            HSSFRow hssfRow = hssfSheet.createRow(rowNum++);
            //列
            int j = 0;
            if (!CommonUtil.listIsBlank(list)) {
                if (enableTitle) {
                    for (Object i : list.get(0).keySet()) {
                        //新建第一行
                        hssfRow.createCell(j++).setCellValue(i.toString());
                    }
                }
                //将数据放入表中
                for (int i = 0; i < list.size(); i++) {
                    //新建一行
                    HSSFRow row = hssfSheet.createRow(rowNum++);
                    Map map = list.get(i);
                    j = 0;
                    for (Object obj : map.values()) {
                        if (obj != null) {
                            row.createCell(j++).setCellValue(obj.toString());
                        } else {
                            row.createCell(j++);
                        }
                    }
                }
            }
            hssfWorkbook.write(response.getOutputStream());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出实体集合到excel
     *
     * @param response
     * @param name
     * @param list
     */
    public static void exportEntityToExcel(HttpServletResponse response, String name, List<Document> list, List<EntitySchema.AttrVO> attrs) throws BusinessException {
        response.setHeader("Access-Control-Expose-Headers", "content-disposition");
        try {
            response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            throw new BusinessException(ErrCode.EXPORT_ERROR);
        }
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(name);
        int rowNum = 0;
        //新建行
        HSSFRow hssfRow = hssfSheet.createRow(rowNum++);
        //列
        int j = 0;
        if (!CommonUtil.listIsBlank(list)) {
            Document title = list.get(0);
            //排序
            Set<String> titleSet = title.keySet();
            List<String> titlesList = new ArrayList<>();
            for (EntitySchema.AttrVO attrVO : attrs) {
                if (titleSet.contains(attrVO.getCode())) titlesList.add(attrVO.getCode());
            }
            for (String i : titlesList) {
                //新建第一行
                hssfRow.createCell(j++).setCellValue(getAttrName(i, attrs));
            }
            //子表单
            EntitySchema.AttrVO subArrayCode = getSubArrayCode(attrs);
            List<EntitySchema.AttrVO> subAttrs = null;
            List<String> subTilteList = null;
            if (!ObjectUtils.isEmpty(subArrayCode)) {
                if ("objArray".equals(subArrayCode.getType())) {
                    subAttrs = ((List<Map>) subArrayCode.getReference()).stream().map(m -> DataUtil.o2o(m, EntitySchema.AttrVO.class)).collect(Collectors.toList());
                } else {
                    subAttrs = (List<EntitySchema.AttrVO>) subArrayCode.getReference();
                }
                subTilteList = subAttrs.stream().map(EntitySchema.AttrVO::getCode).collect(Collectors.toList());
                for (String i : subTilteList) {
                    //新建第一行
                    hssfRow.createCell(j++).setCellValue(getAttrName(i, subAttrs));
                }
            }

            //将数据放入表中
            for (int i = 0; i < list.size(); i++) {
                Document map = list.get(i);
                if (!ObjectUtils.isEmpty(subArrayCode)) {
                    List<Document> subList = map.getList(subArrayCode.getCode(), Document.class);
                    if (ObjectUtils.isEmpty(subList)) {
                        //新建一行
                        HSSFRow row = hssfSheet.createRow(rowNum++);
                        fillData(row, titlesList, attrs, map, 0);
                    } else {
                        for (int k = 0; k < subList.size(); k++) {
                            //新建一行
                            HSSFRow row = hssfSheet.createRow(rowNum++);
                            j = fillData(row, titlesList, attrs, map, 0);
                            //子表单填充
                            fillData(row, subTilteList, subAttrs, subList.get(k), j);
                        }
                    }
                } else {
                    //新建一行
                    HSSFRow row = hssfSheet.createRow(rowNum++);
                    fillData(row, titlesList, attrs, map, 0);
                }
            }
        }
        try {
            hssfWorkbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 每行填充数据
     */
    private static int fillData(HSSFRow row, List<String> titlesList, List<EntitySchema.AttrVO> attrs, Document map, int j1) {
        int j = j1;
        for (int index = 0; index < titlesList.size(); index++) {
            EntitySchema.AttrVO attrVO = getAttr(titlesList.get(index), attrs);
            String type = attrVO.getType();
            Object obj = map.get(titlesList.get(index));
            if (!ObjectUtils.isEmpty(obj)) {
                String valueFormat = attrVO.getValueFormat();
                if ("timeText".equals(type)) {
                    Date date;
                    if (obj instanceof Date) {
                        date = (Date) obj;
                    } else if (obj instanceof Long) {
                        date = new Date((Long) obj);
                    } else {
                        date = new Date();
                    }
                    row.createCell(j++).setCellValue(DateUtil.getDateStr(StringUtils.isEmpty(valueFormat) ? new SimpleDateFormat(
                            "yyyy-MM-dd HH:mm:ss") : new SimpleDateFormat(valueFormat), date));
                } else if ("objArray".equals(type)) {
                    row.createCell(j++);
                } else if ("arraySubObj".equals(type)) {
                    row.createCell(j++);
                } else if ("richText".equals(type)) {
                    row.createCell(j++);
                } else if ("multiObject".equals(type)) {
                    row.createCell(j++);
                } else if (obj instanceof Document) {
                    String value = ((Document) obj).getString("name");
                    if (StringUtils.isEmpty(value)) {
                        value = ((Document) obj).getString("名称");
                    }
                    if (StringUtils.isEmpty(value)) {
                        value = ((Document) obj).getString("id");
                    }
                    row.createCell(j++).setCellValue(value);
                } else if ("binaryText".equals(type)) {
                    row.createCell(j++).setCellValue("https://ip/s1/basic/file/" + obj.toString());
                } else {
                    row.createCell(j++).setCellValue(obj.toString());
                }

            } else {
                row.createCell(j++);
            }
        }
        return j;
    }

    /**
     * 导出集合到excel
     *
     * @param response
     * @param name
     * @param titles
     */
    public static void buildExcelTemplate(HttpServletResponse response, String name, List<String> titles) throws BusinessException {
        response.setHeader("Access-Control-Expose-Headers", "content-disposition");
        // 下载文件的默认名称
        try {
            response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            throw new BusinessException(ErrCode.EXPORT_ERROR);
        }
        try {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            HSSFSheet hssfSheet = hssfWorkbook.createSheet(name);
            //新建行
            HSSFRow hssfRow = hssfSheet.createRow(0);
            int j = 0;
            for (String i : titles) {
                //新建第一行
                hssfRow.createCell(j++).setCellValue(i);
            }
            hssfWorkbook.write(response.getOutputStream());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /***
     * 解析Excel日期格式
     * @param strDate
     * @return
     */
    public static String ExcelDoubleToDate(String strDate) {
        if (strDate.length() == 5) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date tDate = DoubleToDate(Double.parseDouble(strDate));
                return sdf.format(tDate);
            } catch (Exception e) {
                e.printStackTrace();
                return strDate;
            }
        }
        return strDate;
    }

    /**
     * 解析Excel日期格式
     *
     * @param dVal
     * @return
     */
    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        long localOffset = tDate.getTimezoneOffset() * 60000;
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));
        return tDate;
    }
}

